SpreadBookMarkup

V8 Message Definiton

Live spread quotes with SpiderRock markup details for equity and future option spreads.

METADATA

Attribute	Value
Topic	2895-market-data-spreads
MLink Token	SpreadMktData
Product	SRSpread
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
skey_at	enum - AssetType	PRI	'None'	SR Spread Key should have corresponding ProductDefinition record
skey_ts	enum - TickerSrc	PRI	'None'	SR Spread Key should have corresponding ProductDefinition record
skey_tk	VARCHAR(12)	PRI	''	SR Spread Key should have corresponding ProductDefinition record
isTest	enum - YesNo	PRI	'None'	Yes indicates that response is made of entirely of isTestYes SpreadExchOrders
ticker_at	enum - AssetType		'None'	common spread underlier
ticker_ts	enum - TickerSrc		'None'	common spread underlier
ticker_tk	VARCHAR(12)		''	common spread underlier
SRspreadID	BIGINT		0
priceFormat	enum - PriceFormat		'None'
bidPrice1	DOUBLE		0	bid price
isBidPrice1Valid	enum - YesNo		'None'
askPrice1	DOUBLE		0	ask price
isAskPrice1Valid	enum - YesNo		'None'
bidSize1	INT		0	cumulative size at bidPrice
askSize1	INT		0	cumulative size at askPrice
bidMask1	INT UNSIGNED		0	exchange bid bit mask OptExch mask for NMS spreads zero for single exchange spreads
askMask1	INT UNSIGNED		0	exchange ask bit mask OptExch mask for NMS spreads zero for single exchange spreads
bidExch1	enum - OptExch		'None'	exchange at bid price with the largest size if any
askExch1	enum - OptExch		'None'	exchange at ask price with the largest size if any
bidTime	DATETIME(6)		'1900-01-01 00:00:00.000000'	last bid price or size change
askTime	DATETIME(6)		'1900-01-01 00:00:00.000000'	last ask price or size change
updateType	enum - UpdateType		'None'
numStkLegs	TINYINT UNSIGNED		0
numFutLegs	TINYINT UNSIGNED		0
numOptLegs	TINYINT UNSIGNED		0
userDefined	enum - YesNo		'None'
spreadClass	enum - ToolSpreadClass		'None'
containsHedge	enum - YesNo		'None'
legBidPrc	DOUBLE		0
legAskPrc	DOUBLE		0
legBidSz	INT		0
legAskSz	INT		0
surfPrc	DOUBLE		0
surfDelta	FLOAT		0
surfGamma	FLOAT		0
surfVega	FLOAT		0
surfWtVega	FLOAT		0
surfError	TINYINT UNSIGNED		0	error code from surface price calc if any
minExpiry	DATETIME(6)		'1900-01-01 00:00:00.000000'	expiry of earliest option legs
maxExpiry	DATETIME(6)		'1900-01-01 00:00:00.000000'	expiry of latest option legs
minYears	FLOAT		0
maxYears	FLOAT		0
refUPrc	FLOAT		0	reference underlier price
printPrice	FLOAT		0	last spread print price if any
printTime	DATETIME(6)		'1900-01-01 00:00:00.000000'
printSize	INT		0	last spread print size if any
printVolume	INT		0
grpNum	INT		0
securityDesc	VARCHAR(24)		''
filterId	BIGINT		0	tool server filter request ID
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'
MarkupLegsList	JSON		'JSON_OBJECT()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
skey_tk	1
skey_at	2
skey_ts	3
isTest	4

JSON Block (MarkupLegsList)

Field	Type	Comment
legSecKey	OptionKey
legSecType	enum - legSecType
legPriceFormat	enum - legPriceFormat
legSecurityDesc	string	Security description
legSide	enum - legSide
legRatio	ushort	leg ratio 1 2 etc
legBidPrice	double	leg market bid
legBidSize	int
legAskPrice	double	leg market ask
legAskSize	int
legYears	float
legUPrc	double
legSurfVol	float	leg surface volatility
legSurfPrice	float	surface price as of message arrival
legSurfDe	float
legSurfGa	float
legSurfVe	float
legSurfErr	byte

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRSpread`.`MsgSpreadBookMarkup` (
    `skey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'SR Spread Key (should have corresponding ProductDefinition record)',
    `skey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'SR Spread Key (should have corresponding ProductDefinition record)',
    `skey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'SR Spread Key (should have corresponding ProductDefinition record)',
    `isTest` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'Yes indicates that response is made of entirely of isTest=Yes SpreadExchOrders',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'common spread underlier',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'common spread underlier',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'common spread underlier',
    `SRspreadID` BIGINT NOT NULL DEFAULT 0,
    `priceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None',
    `bidPrice1` DOUBLE NOT NULL DEFAULT 0 COMMENT 'bid price',
    `isBidPrice1Valid` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `askPrice1` DOUBLE NOT NULL DEFAULT 0 COMMENT 'ask price',
    `isAskPrice1Valid` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `bidSize1` INT NOT NULL DEFAULT 0 COMMENT 'cumulative size at bidPrice',
    `askSize1` INT NOT NULL DEFAULT 0 COMMENT 'cumulative size at askPrice',
    `bidMask1` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'exchange bid bit mask (OptExch mask for NMS spreads; zero for single exchange spreads)',
    `askMask1` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'exchange ask bit mask (OptExch mask for NMS spreads; zero for single exchange spreads)',
    `bidExch1` ENUM('None','AMEX','BOX','CBOE','ISE','NYSE','PHLX','NSDQ','BATS','C2','NQBX','MIAX','GMNI','CME','CBOT','NYMEX','COMEX','ICE','EDGO','MCRY','MPRL','SDRK','DQTE','EMLD','CFE','MEMX','EUREX','CEDX','NXAM','NXBR','NXLS','NXML','NXOS','NXP','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'exchange at bid price with the largest size (if any)',
    `askExch1` ENUM('None','AMEX','BOX','CBOE','ISE','NYSE','PHLX','NSDQ','BATS','C2','NQBX','MIAX','GMNI','CME','CBOT','NYMEX','COMEX','ICE','EDGO','MCRY','MPRL','SDRK','DQTE','EMLD','CFE','MEMX','EUREX','CEDX','NXAM','NXBR','NXLS','NXML','NXOS','NXP','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'exchange at ask price with the largest size (if any)',
    `bidTime` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'last bid price or size change',
    `askTime` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'last ask price or size change',
    `updateType` ENUM('None','PrcChange','SizeOnly','PrevPeriod') NOT NULL DEFAULT 'None',
    `numStkLegs` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `numFutLegs` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `numOptLegs` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `userDefined` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `spreadClass` ENUM('None','Stk','Fut','Call','Put','Synth','RevCon','Box','JRoll','Roll','Straddle','Strangle','CSpread','PSpread','VStrip','VSpread','HStrip','HSpread','BFly','RiskRev','Mixed','VarSwap') NOT NULL DEFAULT 'None',
    `containsHedge` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `legBidPrc` DOUBLE NOT NULL DEFAULT 0,
    `legAskPrc` DOUBLE NOT NULL DEFAULT 0,
    `legBidSz` INT NOT NULL DEFAULT 0,
    `legAskSz` INT NOT NULL DEFAULT 0,
    `surfPrc` DOUBLE NOT NULL DEFAULT 0,
    `surfDelta` FLOAT NOT NULL DEFAULT 0,
    `surfGamma` FLOAT NOT NULL DEFAULT 0,
    `surfVega` FLOAT NOT NULL DEFAULT 0,
    `surfWtVega` FLOAT NOT NULL DEFAULT 0,
    `surfError` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'error code from surface price calc (if any)',
    `minExpiry` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'expiry of earliest option leg(s)',
    `maxExpiry` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'expiry of latest option leg(s)',
    `minYears` FLOAT NOT NULL DEFAULT 0,
    `maxYears` FLOAT NOT NULL DEFAULT 0,
    `refUPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'reference underlier price',
    `printPrice` FLOAT NOT NULL DEFAULT 0 COMMENT 'last spread print price (if any)',
    `printTime` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `printSize` INT NOT NULL DEFAULT 0 COMMENT 'last spread print size (if any)',
    `printVolume` INT NOT NULL DEFAULT 0,
    `grpNum` INT NOT NULL DEFAULT 0,
    `securityDesc` VARCHAR(24) NOT NULL DEFAULT '',
    `filterId` BIGINT NOT NULL DEFAULT 0 COMMENT 'tool server filter request ID',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `MarkupLegsList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(MarkupLegsList)),
    PRIMARY KEY USING HASH (`skey_tk`,`skey_at`,`skey_ts`,`isTest`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='Live spread quotes with SpiderRock markup details for equity and future option spreads.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `skey_at`,
    `skey_ts`,
    `skey_tk`,
    `isTest`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `SRspreadID`,
    `priceFormat`,
    `bidPrice1`,
    `isBidPrice1Valid`,
    `askPrice1`,
    `isAskPrice1Valid`,
    `bidSize1`,
    `askSize1`,
    `bidMask1`,
    `askMask1`,
    `bidExch1`,
    `askExch1`,
    `bidTime`,
    `askTime`,
    `updateType`,
    `numStkLegs`,
    `numFutLegs`,
    `numOptLegs`,
    `userDefined`,
    `spreadClass`,
    `containsHedge`,
    `legBidPrc`,
    `legAskPrc`,
    `legBidSz`,
    `legAskSz`,
    `surfPrc`,
    `surfDelta`,
    `surfGamma`,
    `surfVega`,
    `surfWtVega`,
    `surfError`,
    `minExpiry`,
    `maxExpiry`,
    `minYears`,
    `maxYears`,
    `refUPrc`,
    `printPrice`,
    `printTime`,
    `printSize`,
    `printVolume`,
    `grpNum`,
    `securityDesc`,
    `filterId`,
    `timestamp`,
    `MarkupLegsList`
FROM `SRSpread`.`MsgSpreadBookMarkup`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `skey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `skey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `skey_tk` = 'Example_skey_tk'
  AND
    /* Replace with a ENUM('None','Yes','No') */ 
    `isTest` = 'None';

Doc Columns Query

SELECT * FROM SRSpread.doccolumns WHERE TABLE_NAME='SpreadBookMarkup' ORDER BY ordinal_position ASC;